package controllers;

import play.*;
import play.db.DB;
import play.mvc.*;

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import com.google.gson.Gson;

import lib.UtilsInput;
import models.*;

public class Reporte_6 extends Controller {
	
	public static void Reporte_6() {
		if(Security.isAuthenticated())
			render();
		else
			Application.login();
	}
	
	public static void getReporte6(String from,String to){
		String query = "SELECT SUM(aceptadas) AS aceptadas, SUM(rechazadas) AS rechazadas, cot_servicio AS servicio " +
						"FROM " +
						"(SELECT COUNT(*) AS aceptadas,0 rechazadas, cot_servicio " +
						"FROM sigpc_cotizacion " +
						"WHERE cot_estado = 3 " +
						"GROUP BY cot_servicio " +
						"UNION " +
						"SELECT 0 aceptadas, COUNT(*) AS rechazadas, cot_servicio " +
						"FROM sigpc_cotizacion " +
						"WHERE cot_estado = 4 " +
						"GROUP BY cot_servicio) " +
						"GROUP BY cot_servicio";
		String str = null;
		Gson gson = new Gson();
		Connection conn = DB.getConnection();
	
		/*
		 * Lista de objetos 
		 */
		List<Reporte6> listReporte = new ArrayList<Reporte6>();
		try {
			if (from != null &&  to != null)
			{
				if (!from.equalsIgnoreCase("") &&  !to.equalsIgnoreCase("") )
					query = "SELECT SUM(aceptadas) AS aceptadas, SUM(rechazadas) AS rechazadas, cot_servicio AS servicio " +
							"FROM " +
							"(SELECT COUNT(*) AS aceptadas,0 rechazadas, cot_servicio " +
							"FROM sigpc_cotizacion " +
							"WHERE cot_estado = 3 AND( cot_fecha BETWEEN '" + from + "' AND '" + to + "')" +
							"GROUP BY cot_servicio " +
							"UNION " +
							"SELECT 0 aceptadas, COUNT(*) AS rechazadas, cot_servicio " +
							"FROM sigpc_cotizacion " +
							"WHERE cot_estado = 4 AND( cot_fecha BETWEEN '" + from + "' AND '" + to + "')" +
							"GROUP BY cot_servicio) " +
							"GROUP BY cot_servicio";
			}
		
		
		ResultSet rs = conn.createStatement().executeQuery(query);
		
		 while (rs.next()) {
			 Reporte6 reporte = new Reporte6(rs.getRow(), rs.getString("servicio"), rs.getInt("aceptadas"),rs.getInt("rechazadas"));
			 listReporte.add(reporte);
		 }
		 
		 if(listReporte.size()!=0){
			 str = "{\"success\":true ,\"records\":" + listReporte.size()
						+ ", \"total\":1 , \"page\":1, \"rows\":" + gson.toJson(listReporte)
						+ "}";				
			 renderJSON(str);			 
		 }
		 else{
			 str = "{\"success\":false,\"records\":" + listReporte.size()
						+ ", \"total\":0 , \"page\":0}";
				
				 renderJSON(str);
		 }		 
     	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//JASPER REPORT XLS
		public static void  download_report(String from, String to, String formato) {
		    Map reportParams = new HashMap();
		    
		   if (formato != null)
		   {
		  reportParams.put("from", from);
		  reportParams.put("to",  to);
		    
		  if (formato.equalsIgnoreCase("html"))
			renderHtml( UtilsInput.toString((ByteArrayInputStream) lib.BaseJasperReport.generateReport("reporte_6", reportParams,formato)));
			
		  else
			  renderBinary(lib.BaseJasperReport.generateReport("reporte_6", reportParams,formato), "reporte_6."+ formato);
		   }
	}
	
}